﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Mapeo;
using BE;
using NHibernate;
using System.Data;
using MySql.Data.MySqlClient;
using NHibernate.Criterion;

namespace DAL
{
    public class MigracionNotas_DAL:Controlador
    {
        public DataTable ObtenerPeriodos()
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "select id, name from mdl_course_categories cat inner join data_Periodo per on cat.idnumber=per.Semestre where parent=0 and coursecount=0 and per.MigracionMatriculaActivo=1 order by sortorder desc";
                    SqlCmd.CommandType = System.Data.CommandType.Text;
                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }
        public DataTable ObtenerCategoriasMoodle(String IDPadre)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);


                    SqlCmd.CommandText = "SELECT id, name from mdl_course_categories where parent=(select id from mdl_course_categories where id=" + IDPadre + ")";
                    

                    SqlCmd.CommandType = System.Data.CommandType.Text;
                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }
        public DataTable ObtenerCursosMoodle(String IDCategoria)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);


                    SqlCmd.CommandText = "select id, fullname from mdl_course where category=" + IDCategoria + " order by sortorder asc";


                    SqlCmd.CommandType = System.Data.CommandType.Text;
                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }
        public DataTable ListadoNotasMoodle(Int32 IDSemeste, Int32 IDEspecialidad, Int32 IDCiclo, Int32 IDCurso, Int32 IDAlumno)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);


                    SqlCmd.CommandText = "sp_ListadoCursosMoodle";
                    SqlCmd.Parameters.AddWithValue("@in_Semestre", IDSemeste);
                    SqlCmd.Parameters.AddWithValue("@in_Especialidad", IDEspecialidad);
                    SqlCmd.Parameters.AddWithValue("@in_Ciclo", IDCiclo);
                    SqlCmd.Parameters.AddWithValue("@in_Curso", IDCurso);
                    SqlCmd.Parameters.AddWithValue("@in_Alumno", IDAlumno);


                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }
        public DataTable ListadoAlumnoMoodle(Int32 IDSemeste, Int32 IDEspecialidad, Int32 IDCiclo)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);


                    SqlCmd.CommandText = "sp_ListadoAlumnosMoodle";
                    SqlCmd.Parameters.AddWithValue("@in_Semestre", IDSemeste);
                    SqlCmd.Parameters.AddWithValue("@in_Especialidad", IDEspecialidad);
                    SqlCmd.Parameters.AddWithValue("@in_Ciclo", IDCiclo);



                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }





        public void MigracionNotasStep1(String IDSemeste, String IDEspecialidad, String IDCiclo, String IDCurso, String IDAlumno, String Usuario)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);

                    SqlCmd.CommandText = "sp_MigrarNotasDesdeMoodle_Step1";
                    SqlCmd.Parameters.AddWithValue("@inSemestre", IDSemeste);
                    SqlCmd.Parameters.AddWithValue("@inEspecialidad", IDEspecialidad);
                    SqlCmd.Parameters.AddWithValue("@inCiclo", IDCiclo);
                    SqlCmd.Parameters.AddWithValue("@inCurso", IDCurso);
                    SqlCmd.Parameters.AddWithValue("@inAlumno", IDAlumno);
                    SqlCmd.Parameters.AddWithValue("@inUsuario", Usuario);


                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.ExecuteNonQuery();
                    Trans.Commit();
                }
            }
        }


        public void MigracionNotasStep2(String IDSemeste, String IDEspecialidad, String IDCiclo, String IDCurso, String IDAlumno)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);

                    SqlCmd.CommandText = "sp_MigrarNotasDesdeMoodle_Step2";
                    SqlCmd.Parameters.AddWithValue("@inSemestre", IDSemeste);
                    SqlCmd.Parameters.AddWithValue("@inEspecialidad", IDEspecialidad);
                    SqlCmd.Parameters.AddWithValue("@inCiclo", IDCiclo);
                    SqlCmd.Parameters.AddWithValue("@inCurso", IDCurso);
                    SqlCmd.Parameters.AddWithValue("@inAlumno", IDAlumno);

                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.ExecuteNonQuery();
                    Trans.Commit();
                }
            }
        }



        public void MigracionNotasStep3(String IDSemeste, String IDEspecialidad, String IDCiclo, String IDAlumno)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_MigrarNotasDesdeMoodle_Step3";
                    SqlCmd.Parameters.AddWithValue("@inSemestre", IDSemeste);
                    SqlCmd.Parameters.AddWithValue("@inEspecialidad", IDEspecialidad);
                    SqlCmd.Parameters.AddWithValue("@inCiclo", IDCiclo);
                    SqlCmd.Parameters.AddWithValue("@inAlumno", IDAlumno);
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.ExecuteNonQuery();
                    Trans.Commit();
                }
            }
        }




        public CursoCategoria ObtenerCursoCategoriaPorId(Int32 ID)
        {
            try
            {
                using (ISession Sesion = m_SessionFactory.OpenSession())
                {
                    CursoCategoria cp = (CursoCategoria)Sesion.CreateCriteria("CursoCategoria").Add(Restrictions.Eq("IDCp", ID)).UniqueResult();
                    return cp;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        public CursoMoodle ObtenerCursoMoodlePorId(Int32 ID)
        {
            try
            {
                using (ISession Sesion = m_SessionFactory.OpenSession())
                {
                    CursoMoodle cp = (CursoMoodle)Sesion.CreateCriteria("CursoMoodle").Add(Restrictions.Eq("IDCp", ID)).UniqueResult();
                    return cp;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }


        public UsuarioMoodle ObtenerUsuarioMoodlePorId(Int32 ID)
        {
            try
            {
                using (ISession Sesion = m_SessionFactory.OpenSession())
                {
                    UsuarioMoodle cp = (UsuarioMoodle)Sesion.CreateCriteria("UsuarioMoodle").Add(Restrictions.Eq("IDCp", ID)).UniqueResult();
                    return cp;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }


    }
}
